package nxu.it.service;

import nxu.it.entity.Category;
import nxu.it.entity.Comment;
import nxu.it.entity.Post;
import org.jdbi.v3.core.Jdbi;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * @author yangliu
 * @project jfinal-app-1
 * @sid 12020243892
 * @date 2022/12/22 - 12:48
 */
public class PostService {


    private static final Logger LOGGER = LoggerFactory.getLogger("nxu.it");
    Jdbi jdbi = DbHelper.JDBI;

    public List<Category> selectCategory() {
        String sql = "select * from t_category ";
        List<Category> categories = DbHelper.JDBI.withHandle(handle -> handle.createQuery(sql)
                .mapToBean(Category.class)
                .list());
        return categories;
    }

    public List<Category> selectCategory(int pageNum) {
        // 计算偏移量
        int finalPageNum = (pageNum - 1) * 10;
        String sql = "select * from t_category LIMIT ?,10";
        List<Category> categories = DbHelper.JDBI.withHandle(handle -> handle.createQuery(sql).bind(0, finalPageNum)
                .mapToBean(Category.class)
                .list());
        return categories;
    }

    public Boolean updateCategory(Category category) {
        String sql = "UPDATE t_category SET name=:name WHERE id=:id";
        return 1 == jdbi.withHandle(handle -> handle.createUpdate(sql).bindBean(category).execute());
    }


    public Boolean insertCategory(String category) {
        String insertCategorySql = "insert into t_category(name) "
                + "VALUES ( :name)";
        boolean flag = false;
        try {
            DbHelper.JDBI.withHandle(handle -> handle.createUpdate(insertCategorySql)
                    .bind("name", category).execute());
            flag = true;
        } catch (RuntimeException e) {
            LOGGER.error("向数据库中插入分类{}出错，原因：{}", category, e.getMessage());
        }
        return flag;
    }


    //新增评论
    public static Boolean insertComment(String comment, int pid) {
        String insertCommentSql = "insert into t_comment(comment) "
                + "VALUES ( :comment)";
        boolean flag = false;
        try {
            DbHelper.JDBI.withHandle(handle -> handle.createUpdate(insertCommentSql)
                    .bind("comment", comment).execute());
            flag = true;
        } catch (RuntimeException e) {
            LOGGER.error("向数据库中插入分类{}出错，原因：{}", comment, e.getMessage());
        }
        return flag;
    }

    //查询评论
    public static List<Comment> selectComment(int pid) {
        String findAllCommentSql = "select * from t_comment where pid = ? ";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findAllCommentSql).bind(0, pid)
                .mapToBean(Comment.class)
                .list()
        );
    }


    //发表文章
    public boolean insertPost(Post post) {
        String insertPostSql = "insert into t_post(category_id, title, content) "
                + "VALUES ( :categoryId, :title, :username, :content)";
        boolean success = false;
        try {
            DbHelper.JDBI.withHandle(handle -> handle.createUpdate(insertPostSql)
                    .bindBean(post)
                    .execute());
            success = true;
        } catch (RuntimeException e) {
            LOGGER.error("向数据库中插入文章{}出错，原因：{}", post, e.getMessage());
        }
        return success;
    }

    //根据cid查询文章
    public List<Post> selectPostsByByCategoryId(Integer cid) {
        String findByCategoryIdSql = "SELECT * FROM t_post WHERE category_id= ?";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findByCategoryIdSql).bind(0, cid)
                .mapToBean(Post.class).list());
    }

    public List<Post> selectPostsByByCategoryId(Integer cid, Integer pageNum) {
        // 计算偏移量
        int finalPageNum = (pageNum - 1) * 10;
        String findByCategoryIdSql = "SELECT * FROM t_post WHERE category_id= ? LIMIT ?,10";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findByCategoryIdSql)
                .bind(0, cid).bind(1, finalPageNum)
                .mapToBean(Post.class).list());
    }

    //根据id查询文章
    public Post selectPostById(int pid) {
        String findByIdSql = "SELECT * FROM t_post WHERE id= ?";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findByIdSql)
                .bind(0, pid).mapToBean(Post.class).first());
    }

    public List<Comment> selectCommentByPid(Integer pid, Integer pageNum) {
        // 计算偏移量
        int finalPageNum = (pageNum - 1) * 10;
        String findByCategoryIdSql = "SELECT * FROM t_comment WHERE pid= ? LIMIT ?,10";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findByCategoryIdSql).bind(0, pid).bind(1, finalPageNum)
                .mapToBean(Comment.class).list());
    }

    public Category selectCategoryByCid(Integer cid) {
        String findByIdSql = "SELECT * FROM t_category WHERE id= ?";
        return DbHelper.JDBI.withHandle(handle -> handle.createQuery(findByIdSql)
                .bind(0, cid).mapToBean(Category.class).one());
    }

    public boolean deleteCategory(Integer cid) {
        String sql = "DELETE FROM t_category  WHERE id=:id";
        return 1 == jdbi.withHandle(handle -> handle.createUpdate(sql).bind("id", cid).execute());
    }

    public boolean deleteComment(Integer cid) {
        return false;
    }

    public boolean deletePost(Integer pid) {
        return false;
    }

    public boolean updatePost(Post post) {
        return false;
    }
}
